package com.cpi.cornpp.exchangerateversion.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.exchangerateversion.exception.ExchangeRateVersionException;
import com.cpi.cornpp.exchangerateversion.dto.ExchangeRateVersionDTO;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class ExchangeRateVersionDAO extends DBDAO {
	/** SQL select exchange rate version by id*/
	private static final String SQL_SELECT_EXCHANGE_RATE_VERSION_BY_ID = "SELECT MON.MONTH_PERIOD_VERSION_ID, EXC.CURRENCY_ID, EXC.AMOUNT, EXC.AVG_AMOUNT FROM MONTHS_PERIODS_VERSIONS MON INNER JOIN EXCHANGES_RATES_VERSIONS EXC ON MON.MONTH_PERIOD_VERSION_ID = EXC.MONTH_PERIOD_VERSION_ID INNER JOIN MONTHS_PERIODS MP ON MON.MONTH_PERIOD_ID = MP.MONTH_PERIOD_ID WHERE MON.VERSION_ID = ? ORDER BY MP.START_DATE";//$NON-NLS-1$
	
	private static final String SQL_UPDATE_EXCHANGE_RATE_VERSION_BY_ID_AND_CURRENCY = "UPDATE EXCHANGES_RATES_VERSIONS SET AMOUNT = ?, AVG_AMOUNT = ? WHERE MONTH_PERIOD_VERSION_ID = ? AND CURRENCY_ID = ?";//$NON-NLS-1$

	/**
	 * Obtener un ExchangeRateVersion por su id
	 * @param versionId
	 * @return
	 * @throws ExchangeRateVersionException
	 */
	public List getExchangeRateVersionById(int versionId)throws ExchangeRateVersionException{
		ExchangeRateVersionDTO exchangeRateVersionDTO = null;
		List listResult = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			listResult = new ArrayList(0);
			pstm = prepareSQL(SQL_SELECT_EXCHANGE_RATE_VERSION_BY_ID, dsName);
			pstm.setInt(1, versionId);
			rs = executeQuery(pstm);
			while (rs.next()){
				exchangeRateVersionDTO = new ExchangeRateVersionDTO();
				exchangeRateVersionDTO.setMonthPeriodVersionId(rs.getInt(1));
				exchangeRateVersionDTO.setCurrency(rs.getString(2));
				exchangeRateVersionDTO.setAmount(rs.getDouble(3));
				exchangeRateVersionDTO.setAvgAmount(rs.getDouble(4));
				listResult.add(exchangeRateVersionDTO);
			}
		}catch (PoolException poolException){
			ExchangeRateVersionException exchangeRateVersionException = null;
			poolException.printStackTrace(System.out);
			exchangeRateVersionException = new ExchangeRateVersionException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw exchangeRateVersionException;
		} catch(SQLException sqlException){
			ExchangeRateVersionException exchangeRateVersionException = null;
			sqlException.printStackTrace(System.out);
			exchangeRateVersionException = new ExchangeRateVersionException (sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw exchangeRateVersionException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return listResult;
	}
	
	/**
	 * Update 
	 * @param 
	 * @return
	 * @throws Exception
	 */
	public void update(ExchangeRateVersionDTO dto) throws Exception{		
		PreparedStatement preparedStatement =  null;
		try {
			preparedStatement =  prepareSQL(SQL_UPDATE_EXCHANGE_RATE_VERSION_BY_ID_AND_CURRENCY, dsName);
			preparedStatement.setDouble(1, dto.getAmount());
			preparedStatement.setDouble(2, dto.getAvgAmount());
			preparedStatement.setInt(3, dto.getMonthPeriodVersionId());
			preparedStatement.setString(4, dto.getCurrency());			
			executeInsert(preparedStatement);
		}catch (SQLException sqlException){
			ExchangeRateVersionException exchangeRateVersionException = null;
			sqlException.printStackTrace(System.out);
			exchangeRateVersionException = new ExchangeRateVersionException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw exchangeRateVersionException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
}